package com.dao.impl;


import com.dao.CountryInfoDAO;
import com.entity.AdminInfo;
import com.entity.CountryInfo;
import util.DBUtil;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class CountryInfoDAOImpl implements CountryInfoDAO {

    @Override
    public int insert(CountryInfo entity) throws SQLException {
        String sql = "INSERT INTO country_info(country_code,country_name_cn,country_name_en,country_name,country_uri) values(?,?,?,?,?)";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(1, entity.getCountryCode());
            pstmt.setObject(2,entity.getCountryNameCn());
            pstmt.setObject(3,entity.getCountryNameEn());
            pstmt.setObject(4,entity.getCountryName());
            pstmt.setObject(5,entity.getCountryURI());
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int updateById(CountryInfo entity) throws SQLException {
        String sql = "update country_info set country_name_cn=?,country_name_en=?,country_name=?,country_uri=? where country_code=?";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(5, entity.getCountryCode());
            pstmt.setObject(1,entity.getCountryNameCn());
            pstmt.setObject(2,entity.getCountryNameEn());
            pstmt.setObject(3,entity.getCountryName());
            pstmt.setObject(4,entity.getCountryURI());
            return pstmt.executeUpdate();
        }
    }

    @Override
    public int deleteById(Serializable id) throws SQLException {
        String sql = "delete from country_info  where country_code=?";
        try(
                Connection conn = DBUtil.getConnection();
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            pstmt.setObject(1, id);
            return pstmt.executeUpdate();
        }
    }

    @Override
    public CountryInfo selectById(Serializable id) throws SQLException {
        String sql = "select * from country_info  where country_code=?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                CountryInfo entity = new CountryInfo();
                entity.setCountryCode(rs.getString("country_code"));
                //如果该字段可能为空
                entity.setCountryNameCn(rs.getString("country_name_cn"));
                entity.setCountryNameEn(rs.getString("country_name_en"));
                entity.setCountryName(rs.getString("country_name"));
                entity.setCountryURI(rs.getString("country_uri"));
                return entity;
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    @Override
    public List<CountryInfo> select(int index, int len) throws SQLException {
        String sql = "select * from country_info  order by country_code limit ?,?";//只要是返回多条记录,一定记得+order by子句
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, index);
            pstmt.setObject(2, len);
            rs = pstmt.executeQuery();
            List<CountryInfo> list = new ArrayList<>();
            while (rs.next()) {
                CountryInfo entity = new CountryInfo();
                entity.setCountryCode(rs.getString("country_code"));
                //如果该字段可能为空
                entity.setCountryNameCn(rs.getString("country_name_cn"));
                entity.setCountryNameEn(rs.getString("country_name_en"));
                entity.setCountryName(rs.getString("country_name"));
                entity.setCountryURI(rs.getString("country_uri"));
                list.add(entity);
            }
            return list;
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }

    @Override
    public long count() throws SQLException {
        String sql = "select count(*) from country_info";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if(rs.next()){
                return rs.getLong(1);
            }else{
                throw new SQLException("没有找到对应的记录");
            }
        }finally {
            DBUtil.close(conn,pstmt,rs);
        }
    }
}
